/**
 * 
 */
package dt.inlee.system.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.collections.map.ListOrderedMap;
import org.hibernate.Criteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import dt.inlee.common.constant.CommonConstant;
import dt.inlee.common.constant.CommonEnumType;
import dt.inlee.common.dao.impl.BaseDao;
import dt.inlee.common.exception.FrameworkException;
import dt.inlee.common.util.BaseUtil;
import dt.inlee.common.util.DateUtil;
import dt.inlee.domain.LogInfo;
import dt.inlee.domain.SysOrganization;
import dt.inlee.system.vo.LogInfoBean;

/**
 * @功能说明：日志DAO
 * @作者： xgyin 
 * @创建日期： 2011-3-22
 */
@Repository("logInfoDao")
public class LogInfoDao extends BaseDao {

	/**
	 * 
	 * @param logInfoBean
	 * @return
	 * @throws FrameworkException
	 */
	public int getLogInfoTotalRows(LogInfoBean logInfoBean) throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory().getCurrentSession().createCriteria(LogInfo.class);
		if (!BaseUtil.isEmpty(logInfoBean)) {
			if (!BaseUtil.isEmpty(logInfoBean.getBeginTime()))
				criteria.add(Restrictions.ge("dtime", logInfoBean.getBeginTime()));
			if (!BaseUtil.isEmpty(logInfoBean.getEndTime()))
				criteria.add(Restrictions.le("dtime", logInfoBean.getEndTime()));
			if (!BaseUtil.isEmpty(logInfoBean.getOptype()))
				criteria.add(Restrictions.eq("optype", logInfoBean.getOptype()));
			if (!BaseUtil.isEmpty(logInfoBean.getOperatorUser()))
				criteria.add(Restrictions.eq("operatorUser", logInfoBean.getOperatorUser()));
			if (!BaseUtil.isEmpty(logInfoBean.getOperatorOrg()))
				criteria.add(Restrictions.eq("operatorOrg", logInfoBean.getOperatorOrg()));
			if (!BaseUtil.isEmpty(logInfoBean.getObjectname()))
				criteria.add(Restrictions.eq("objectname", logInfoBean.getObjectname()));
		}
		criteria.add(Restrictions.eq("state", CommonEnumType.LogState.正常.getKey()));
		criteria.setProjection(Projections.countDistinct("id"));
		@SuppressWarnings("unchecked")
        List<Integer> count = criteria.list();
		return count.get(0).intValue();
	}

	/**
	 * 
	 * @param orderBy 
	 * @param order 
	 * @param logInfoBean
	 * @param curPage
	 * @param pageSize
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public List<LogInfo> findAllLogInfos(String order,String dtime, LogInfoBean logInfoBean, int curPage,
			int pageSize) throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory().getCurrentSession().createCriteria(LogInfo.class);
			if (!BaseUtil.isEmpty(logInfoBean.getBeginTime()))
				criteria.add(Restrictions.ge("dtime", logInfoBean.getBeginTime()));
			if (!BaseUtil.isEmpty(logInfoBean.getEndTime()))
				criteria.add(Restrictions.le("dtime", logInfoBean.getEndTime()));
			if (!BaseUtil.isEmpty(logInfoBean.getOptype()))
				criteria.add(Restrictions.eq("optype", logInfoBean.getOptype()));
			if (!BaseUtil.isEmpty(logInfoBean.getOperatorUser()))
				criteria.add(Restrictions.eq("operatorUser", logInfoBean.getOperatorUser()));
			if (!BaseUtil.isEmpty(logInfoBean.getOperatorOrg()))
				criteria.add(Restrictions.eq("operatorOrg", logInfoBean.getOperatorOrg()));
			if (!BaseUtil.isEmpty(logInfoBean.getObjectname()))
				criteria.add(Restrictions.eq("objectname", logInfoBean.getObjectname()));
		
		criteria.add(Restrictions.eq("state", CommonEnumType.LogState.正常.getKey()));
		if (!BaseUtil.isEmpty(order) && !BaseUtil.isEmpty(dtime)) {
			if (order.equalsIgnoreCase("asc")) {
				criteria.addOrder(Order.asc(dtime));
			} else
				criteria.addOrder(Order.desc(dtime));
		}
		if (curPage > 0 && pageSize > 0) {
			criteria.setFirstResult((curPage - 1) * pageSize);
			criteria.setMaxResults(pageSize);
		}
		List<LogInfo> loginfo = criteria.list();
		return loginfo;
	}

	/**
	 * @param reportType
	 * @param orgName
	 * @param beginTime
	 * @param endTime
	 * @return
	 */
	public List<LogInfoBean> logInfoReportCount(String reportType, String orgName, Date beginTime, Date endTime)
			throws FrameworkException {
		StringBuffer sql = new StringBuffer();
		String beginTimeStr = BaseUtil.isEmpty(beginTime) ? null : DateUtil.dateToString(beginTime,
				CommonConstant.DATE_SHORT_FORMAT);
		String endTimeStr = BaseUtil.isEmpty(endTime) ? null : DateUtil.dateToString(endTime,
				CommonConstant.DATE_SHORT_FORMAT);
		if ("0".equalsIgnoreCase(reportType)) {
			sql
					.append("select temp3.addcount, temp3.modifycount, temp3.deletecount,temp3.viewcount,temp3.logincount,  temp3.logoutcount, sysuser.realname operatorUser from sysuser  left join SYSUSER_SYSORGANIZATION userorg on userorg.sysusers_id = sysuser.id  left join SYSORGANIZATION org on org.id = userorg.sysorganizations_id left join ( ");
			sql
					.append("select sum(addcount) addcount, sum(modifycount) modifycount, sum(deletecount) deletecount,sum(viewcount) viewcount,sum(logincount) logincount, sum(logoutcount) logoutcount, operatorUser ");
			sql
					.append("from (select case when optype='修改' then count(*) end modifycount, case when optype='新增' then count(*) end addcount,case when optype='删除' then count(*) end deletecount, case when optype='查看' then count(*) end viewcount,case when optype='登录' then count(*) end logincount, case when optype='退出' then count(*) end logoutcount, optype, operatorUser from logInfo ");
			sql.append("where operatororg = '" + orgName + "' ");
			if (!BaseUtil.isEmpty(beginTimeStr))
				sql.append("and dtime >= to_date('" + beginTimeStr + "','yyyy-MM-dd') ");
			if (!BaseUtil.isEmpty(endTimeStr))
				sql.append("and dtime <= to_date('" + endTimeStr + "','yyyy-MM-dd') ");
			sql.append(" group by  optype,operatorUser) ");
			sql.append("temp group by operatorUser ");
			sql.append(")temp3 on sysuser.realname = temp3.operatorUser  where org.name='" + orgName
					+ "' order by temp3.logincount asc");

		} else if ("1".equalsIgnoreCase(reportType)) {
			String[] split = orgName.split("-");
			int len = split.length;
			if (len == 1) {
				sql
						.append(" select temp3.addcount, temp3.modifycount, temp3.deletecount,temp3.viewcount,temp3.logincount, temp3.logoutcount, sysuser.realname operatoruser from sysuser left join SYSUSER_SYSORGANIZATION userorg on userorg.sysusers_id = sysuser.id left join SYSORGANIZATION org on org.id = userorg.sysorganizations_id left join ( ");
				sql
						.append("select sum(addcount) addcount, sum(modifycount) modifycount, sum(deletecount) deletecount,sum(viewcount) viewcount,sum(logincount) logincount, sum(logoutcount) logoutcount, operatorUser ");
				sql
						.append("from (select case when optype='修改' then count(*) end modifycount, case when optype='新增' then count(*) end addcount,case when optype='删除' then count(*) end deletecount, case when optype='查看' then count(*) end viewcount,case when optype='登录' then count(*) end logincount, case when optype='退出' then count(*) end logoutcount, optype, operatorUser, operatororg from logInfo ");

				sql.append("where operatororg ='" + orgName + "' ");
				if (!BaseUtil.isEmpty(beginTimeStr))
					sql.append("and dtime >= to_date('" + beginTimeStr + "','yyyy-MM-dd') ");
				if (!BaseUtil.isEmpty(endTimeStr))
					sql.append("and dtime <= to_date('" + endTimeStr + "','yyyy-MM-dd') ");
				sql.append(" group by  optype,operatorUser, operatororg) ");
				sql.append("temp group by operatorUser, operatororg ");
				sql.append(")temp3 on sysuser.realname = temp3.operatorUser  where org.name='" + orgName
						+ "' order by temp3.logincount asc");
			} else {
				sql
						.append("select temp3.addcount, temp3.modifycount, temp3.deletecount,temp3.viewcount,temp3.logincount,  temp3.logoutcount, org.name operatoruser from sysorganization org  left join ( ");
				sql
						.append("select sum(addcount) addcount, sum(modifycount) modifycount, sum(deletecount) deletecount,sum(viewcount) viewcount,sum(logincount) logincount, sum(logoutcount) logoutcount, operatororg operatoruser ");
				sql
						.append("from (select case when optype='修改' then count(*) end modifycount, case when optype='新增' then count(*) end addcount,case when optype='删除' then count(*) end deletecount, case when optype='查看' then count(*) end viewcount,case when optype='登录' then count(*) end logincount, case when optype='退出' then count(*) end logoutcount, optype, operatororg from logInfo ");
				sql.append("where operatororg like '" + split[0].substring(2) + "%' ");
				if (!BaseUtil.isEmpty(beginTimeStr))
					sql.append("and dtime >= to_date('" + beginTimeStr + "','yyyy-MM-dd') ");
				if (!BaseUtil.isEmpty(endTimeStr))
					sql.append("and dtime <= to_date('" + endTimeStr + "','yyyy-MM-dd') ");
				sql.append(" group by  optype,operatororg) ");
				sql.append("temp group by operatororg ");
				sql.append(")temp3 on org.name = temp3.operatorUser  where org.name like '" + split[0].substring(2)
						+ "%' order by temp3.logincount asc");
			}

		} else if ("2".equalsIgnoreCase(reportType)) {
			sql
					.append("select temp3.addcount, temp3.modifycount, temp3.deletecount,temp3.viewcount,temp3.logincount,  temp3.logoutcount, sysuser.realname operatoruser from sysuser  left join SYSUSER_SYSORGANIZATION userorg on userorg.sysusers_id = sysuser.id  left join SYSORGANIZATION org on org.id = userorg.sysorganizations_id left join ( ");
			sql
					.append("select sum(addcount) addcount, sum(modifycount) modifycount, sum(deletecount) deletecount,sum(viewcount) viewcount,sum(logincount) logincount, sum(logoutcount) logoutcount, operatorUser ");
			sql
					.append("from (select case when optype='修改' then count(*) end modifycount, case when optype='新增' then count(*) end addcount,case when optype='删除' then count(*) end deletecount, case when optype='查看' then count(*) end viewcount,case when optype='登录' then count(*) end logincount, case when optype='退出' then count(*) end logoutcount, optype, operatorUser, operatororg from logInfo ");
			sql.append("where operatororg ='" + orgName + "' ");
			if (!BaseUtil.isEmpty(beginTimeStr))
				sql.append("and dtime >= to_date('" + beginTimeStr + "','yyyy-MM-dd') ");
			if (!BaseUtil.isEmpty(endTimeStr))
				sql.append("and dtime <= to_date('" + endTimeStr + "','yyyy-MM-dd') ");
			sql.append(" group by  optype,operatorUser, operatororg) ");
			sql.append("temp group by operatorUser, operatororg");
			sql.append(")temp3 on sysuser.realname = temp3.operatorUser  where org.name='" + orgName
					+ "' order by temp3.logincount asc");

		}
		List<ListOrderedMap> result = this.jdbcTemplate.queryForList(sql.toString(),ListOrderedMap.class);
		List<LogInfoBean> loginInfoBeans = new ArrayList<LogInfoBean>();
		for (ListOrderedMap objs : result) {
			LogInfoBean bean = new LogInfoBean();
			Object operatorUser = objs.get("operatorUser");
			if (!BaseUtil.isEmpty(operatorUser))
				bean.setOperatorUser((String) operatorUser);
			Object addcount = objs.get("addcount");
			if (!BaseUtil.isEmpty(addcount))
				bean.setAddCount(((BigDecimal) addcount).intValue());
			Object modifycount = objs.get("modifycount");
			if (!BaseUtil.isEmpty(modifycount))
				bean.setModifyCount(((BigDecimal) modifycount).intValue());
			Object deletecount = objs.get("deletecount");
			if (!BaseUtil.isEmpty(deletecount))
				bean.setDeleteCount(((BigDecimal) deletecount).intValue());
			Object viewcount = objs.get("viewcount");
			if (!BaseUtil.isEmpty(viewcount))
				bean.setViewCount(((BigDecimal) viewcount).intValue());
			Object logincount = objs.get("logincount");
			if (!BaseUtil.isEmpty(logincount))
				bean.setLoginCount(((BigDecimal) logincount).intValue());
			Object logoutcount = objs.get("logoutcount");
			if (!BaseUtil.isEmpty(logoutcount))
				bean.setLogoutCount(((BigDecimal) logoutcount).intValue());
			loginInfoBeans.add(bean);

		}
		return loginInfoBeans;
	}

	/**
	 * 
	 * @param reportType
	 * @param orgName
	 * @param beginTime
	 * @param endTime
	 * @return
	 * @throws FrameworkException
	 */
	public List<LogInfoBean> logInfoSumReportCount(String reportType, String orgName, Date beginTime, Date endTime)
			throws FrameworkException {

		StringBuffer sql = new StringBuffer();
		String beginTimeStr = BaseUtil.isEmpty(beginTime) ? null : DateUtil.dateToString(beginTime,
				CommonConstant.DATE_SHORT_FORMAT);
		String endTimeStr = BaseUtil.isEmpty(endTime) ? null : DateUtil.dateToString(endTime,
				CommonConstant.DATE_SHORT_FORMAT);
		if ("0".equalsIgnoreCase(reportType)) {
			sql
					.append("select (select count (sysuser.realname) from sysuser  left join SYSUSER_SYSORGANIZATION userorg on userorg.sysusers_id = sysuser.id  left join SYSORGANIZATION org on org.id = userorg.sysorganizations_id where org.name='"
							+ orgName + "' ");
			sql.append(") recordcount,temp1.* from (");
			sql
					.append("select sum(addcount) addcount, sum(modifycount) modifycount, sum(deletecount) deletecount,sum(viewcount) viewcount,sum(logincount) logincount, sum(logoutcount) logoutcount ");
			sql
					.append("from (select case when optype='修改' then count(*) end modifycount, case when optype='新增' then count(*) end addcount,case when optype='删除' then count(*) end deletecount, case when optype='查看' then count(*) end viewcount,case when optype='登录' then count(*) end logincount, case when optype='退出' then count(*) end logoutcount, optype from logInfo ");
			sql.append("where operatororg = '" + orgName + "' ");
			if (!BaseUtil.isEmpty(beginTimeStr))
				sql.append("and dtime >= to_date('" + beginTimeStr + "','yyyy-MM-dd') ");
			if (!BaseUtil.isEmpty(endTimeStr))
				sql.append("and dtime <= to_date('" + endTimeStr + "','yyyy-MM-dd') ");
			sql.append(" group by optype) ");
			sql.append("temp)temp1");

		} else if ("1".equalsIgnoreCase(reportType)) {
			sql
					.append("select temp2.recordcount, temp2.operatororg, temp1.addcount, temp1.modifycount, temp1.deletecount, temp1.viewcount, temp1.logincount, temp1.logoutcount from (select count (sysuser.realname) recordcount, org.name operatororg from sysuser left join SYSUSER_SYSORGANIZATION userorg on userorg.sysusers_id = sysuser.id left join SYSORGANIZATION org on org.id = userorg.sysorganizations_id where org.name ='"
							+ orgName + "' group by org.name) temp2 left join ( ");
			sql
					.append("select sum(addcount) addcount, sum(modifycount) modifycount, sum(deletecount) deletecount,sum(viewcount) viewcount,sum(logincount) logincount, sum(logoutcount) logoutcount, operatororg ");
			sql
					.append("from (select case when optype='修改' then count(*) end modifycount, case when optype='新增' then count(*) end addcount,case when optype='删除' then count(*) end deletecount, case when optype='查看' then count(*) end viewcount,case when optype='登录' then count(*) end logincount, case when optype='退出' then count(*) end logoutcount, optype, operatororg from logInfo ");
			sql.append("where operatororg ='" + orgName + "' ");
			if (!BaseUtil.isEmpty(beginTimeStr))
				sql.append("and dtime >= to_date('" + beginTimeStr + "','yyyy-MM-dd') ");
			if (!BaseUtil.isEmpty(endTimeStr))
				sql.append("and dtime <= to_date('" + endTimeStr + "','yyyy-MM-dd') ");
			sql
					.append("group by optype, operatororg) temp group by operatororg) temp1 on temp1.operatororg = temp2.operatororg ");
			sql.append("union ");
			sql
					.append("select (select count (distinct (org.name)) recordcount from sysorganization org where org.name like '"
							+ orgName.substring(2)
							+ "%') recordcount, '分公司',temp2.addcount, temp2.modifycount, temp2.deletecount, temp2.viewcount, temp2.logincount, temp2.logoutcount  from( ");
			sql
					.append("select sum(addcount) addcount, sum(modifycount) modifycount, sum(deletecount) deletecount,sum(viewcount) viewcount,sum(logincount) logincount, sum(logoutcount) logoutcount ");
			sql
					.append("from (select case when optype='修改' then count(*) end modifycount, case when optype='新增' then count(*) end addcount,case when optype='删除' then count(*) end deletecount, case when optype='查看' then count(*) end viewcount,case when optype='登录' then count(*) end logincount, case when optype='退出' then count(*) end logoutcount, optype from logInfo ");
			sql.append("where operatororg like '" + orgName.substring(2) + "%' ");
			if (!BaseUtil.isEmpty(beginTimeStr))
				sql.append("and dtime >= to_date('" + beginTimeStr + "','yyyy-MM-dd') ");
			if (!BaseUtil.isEmpty(endTimeStr))
				sql.append("and dtime <= to_date('" + endTimeStr + "','yyyy-MM-dd') ");
			sql.append("group by optype) temp )temp2 ");
		} else if ("2".equalsIgnoreCase(reportType)) {
			sql
					.append("select temp2.recordcount, temp2.operatororg, temp1.addcount, temp1.modifycount, temp1.deletecount, temp1.viewcount, temp1.logincount, temp1.logoutcount from (select count (sysuser.realname) recordcount, org.name operatororg from sysuser  left join SYSUSER_SYSORGANIZATION userorg on userorg.sysusers_id = sysuser.id  left join SYSORGANIZATION org on org.id = userorg.sysorganizations_id  where org.name in('重庆联通','重庆电信','重庆移动','共建共享办公室','通信管理局') group by org.name) temp2 left join (");
			sql
					.append("select sum(addcount) addcount, sum(modifycount) modifycount, sum(deletecount) deletecount,sum(viewcount) viewcount,sum(logincount) logincount, sum(logoutcount) logoutcount, operatororg ");
			sql
					.append("from (select case when optype='修改' then count(*) end modifycount, case when optype='新增' then count(*) end addcount,case when optype='删除' then count(*) end deletecount, case when optype='查看' then count(*) end viewcount,case when optype='登录' then count(*) end logincount, case when optype='退出' then count(*) end logoutcount, optype, operatororg from logInfo ");
			sql.append("where operatororg in('重庆联通','重庆电信','重庆移动','共建共享办公室','通信管理局') ");
			if (!BaseUtil.isEmpty(beginTimeStr))
				sql.append("and dtime >= to_date('" + beginTimeStr + "','yyyy-MM-dd') ");
			if (!BaseUtil.isEmpty(endTimeStr))
				sql.append("and dtime <= to_date('" + endTimeStr + "','yyyy-MM-dd') ");
			sql
					.append("group by optype, operatororg) temp group by operatororg) temp1 on temp1.operatororg = temp2.operatororg");
		}
		List<ListOrderedMap> result = this.jdbcTemplate.queryForList(sql.toString(),ListOrderedMap.class);
		List<LogInfoBean> loginInfoBeans = new ArrayList<LogInfoBean>();
		for (ListOrderedMap objs : result) {
			LogInfoBean bean = new LogInfoBean();
			Object recordcount = objs.get("recordcount");
			if (!BaseUtil.isEmpty(recordcount))
				bean.setRecordCount(((BigDecimal) recordcount).intValue() + 1);
			bean.setOperatorUser("合计");
			if ("0".equalsIgnoreCase(reportType))
				bean.setBelongsCompany(orgName);
			else {
				Object operatororg = objs.get("operatororg");
				if (!BaseUtil.isEmpty(operatororg))
					bean.setBelongsCompany((String) operatororg);
			}
			Object addcount = objs.get("addcount");
			if (!BaseUtil.isEmpty(addcount))
				bean.setAddCount(((BigDecimal) addcount).intValue());
			Object modifycount = objs.get("modifycount");
			if (!BaseUtil.isEmpty(modifycount))
				bean.setModifyCount(((BigDecimal) modifycount).intValue());
			Object deletecount = objs.get("deletecount");
			if (!BaseUtil.isEmpty(deletecount))
				bean.setDeleteCount(((BigDecimal) deletecount).intValue());
			Object viewcount = objs.get("viewcount");
			if (!BaseUtil.isEmpty(viewcount))
				bean.setViewCount(((BigDecimal) viewcount).intValue());
			Object logincount = objs.get("logincount");
			if (!BaseUtil.isEmpty(logincount))
				bean.setLoginCount(((BigDecimal) logincount).intValue());
			Object logoutcount = objs.get("logoutcount");
			if (!BaseUtil.isEmpty(logoutcount))
				bean.setLogoutCount(((BigDecimal) logoutcount).intValue());
			loginInfoBeans.add(bean);

		}
		return loginInfoBeans;

	}

	/**
	 * 所有的日志组织机构
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public List<SysOrganization> findAllLogOrgs() throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory().getCurrentSession().createCriteria(
				SysOrganization.class);
		return criteria.list();
	}
}
